/**
 * 副将仓库数据迁移脚本
 * 
 * 功能：
 * 1. 检查玩家副将表中超过10个副将的玩家
 * 2. 将多余的副将迁移到仓库表
 * 3. 保持前10个副将在玩家副将表中（按创建时间排序）
 * 4. 生成迁移报告
 */

const mysql = require('mysql2/promise');

// 数据库配置
const DB_CONFIG = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4',
    timezone: '+08:00'
};

class DeputyWarehouseMigrator {
    constructor() {
        this.connection = null;
        this.migrationReport = {
            startTime: new Date().toISOString(),
            endTime: null,
            totalPlayers: 0,
            playersWithExcessDeputies: 0,
            totalDeputiesMoved: 0,
            errors: [],
            details: []
        };
    }

    async connect() {
        try {
            this.connection = await mysql.createConnection(DB_CONFIG);
            console.log('✅ 数据库连接成功');
        } catch (error) {
            console.error('❌ 数据库连接失败:', error);
            throw error;
        }
    }

    async disconnect() {
        if (this.connection) {
            await this.connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }

    /**
     * 检查玩家副将数量统计
     */
    async checkDeputyCounts() {
        console.log('\n📊 检查玩家副将数量统计...');

        const query = `
            SELECT 
                player_id,
                COUNT(*) as deputy_count
            FROM player_deputy 
            GROUP BY player_id 
            HAVING COUNT(*) > 10
            ORDER BY deputy_count DESC
        `;

        const [rows] = await this.connection.execute(query);

        console.log(`📋 发现 ${rows.length} 个玩家拥有超过10个副将:`);
        rows.forEach(row => {
            console.log(`   玩家ID: ${row.player_id}, 副将数量: ${row.deputy_count}`);
        });

        return rows;
    }

    /**
     * 获取玩家副将详细信息
     */
    async getPlayerDeputies(playerId) {
        const query = `
            SELECT 
                pd.id,
                pd.deputy_id,
                pd.pos,
                pd.created_at,
                dg.base_id,
                dgb.name as deputy_name,
                dgb.star_level
            FROM player_deputy pd
            LEFT JOIN deputy_general dg ON pd.deputy_id = dg.id
            LEFT JOIN deputy_general_base dgb ON dg.base_id = dgb.id
            WHERE pd.player_id = ?
            ORDER BY pd.created_at ASC, pd.id ASC
        `;

        const [rows] = await this.connection.execute(query, [playerId]);
        return rows;
    }

    /**
     * 迁移单个玩家的多余副将到仓库
     */
    async migratePlayerDeputies(playerId, deputyCount) {
        console.log(`\n🔄 开始迁移玩家 ${playerId} 的副将 (总数: ${deputyCount})`);

        try {
            // 开始事务
            await this.connection.beginTransaction();

            // 获取玩家所有副将
            const deputies = await this.getPlayerDeputies(playerId);

            if (deputies.length <= 10) {
                console.log(`   ⚠️ 玩家 ${playerId} 的副将数量已正常，跳过迁移`);
                await this.connection.rollback();
                return { moved: 0, kept: deputies.length };
            }

            // 保留前10个副将，其余迁移到仓库
            const keepDeputies = deputies.slice(0, 10);
            const moveDeputies = deputies.slice(10);

            console.log(`   📦 保留 ${keepDeputies.length} 个副将在玩家副将表`);
            console.log(`   🏪 迁移 ${moveDeputies.length} 个副将到仓库表`);

            // 删除多余的副将从玩家副将表
            const deputyIdsToMove = moveDeputies.map(d => d.deputy_id);
            if (deputyIdsToMove.length > 0) {
                const deleteQuery = `
                    DELETE FROM player_deputy 
                    WHERE player_id = ? AND deputy_id IN (${deputyIdsToMove.map(() => '?').join(',')})
                `;
                await this.connection.execute(deleteQuery, [playerId, ...deputyIdsToMove]);
            }

            // 添加到仓库表
            for (const deputy of moveDeputies) {
                const insertQuery = `
                    INSERT INTO player_deputy_warehouse (player_id, deputy_id, created_at, updated_at)
                    VALUES (?, ?, NOW(), NOW())
                `;
                await this.connection.execute(insertQuery, [playerId, deputy.deputy_id]);
            }

            // 提交事务
            await this.connection.commit();

            console.log(`   ✅ 玩家 ${playerId} 迁移完成`);

            return {
                moved: moveDeputies.length,
                kept: keepDeputies.length,
                movedDeputies: moveDeputies.map(d => ({
                    deputy_id: d.deputy_id,
                    name: d.deputy_name,
                    star_level: d.star_level
                }))
            };

        } catch (error) {
            await this.connection.rollback();
            console.error(`   ❌ 玩家 ${playerId} 迁移失败:`, error.message);
            throw error;
        }
    }

    /**
     * 验证迁移结果
     */
    async verifyMigration() {
        console.log('\n🔍 验证迁移结果...');

        const query = `
            SELECT 
                player_id,
                COUNT(*) as deputy_count
            FROM player_deputy 
            GROUP BY player_id 
            HAVING COUNT(*) > 10
        `;

        const [rows] = await this.connection.execute(query);

        if (rows.length === 0) {
            console.log('✅ 验证通过：所有玩家的副将数量都不超过10个');
            return true;
        } else {
            console.log('❌ 验证失败：仍有玩家副将数量超过10个:');
            rows.forEach(row => {
                console.log(`   玩家ID: ${row.player_id}, 副将数量: ${row.deputy_count}`);
            });
            return false;
        }
    }

    /**
     * 生成迁移报告
     */
    generateReport() {
        this.migrationReport.endTime = new Date().toISOString();

        console.log('\n📊 迁移报告:');
        console.log('='.repeat(50));
        console.log(`开始时间: ${this.migrationReport.startTime}`);
        console.log(`结束时间: ${this.migrationReport.endTime}`);
        console.log(`总玩家数: ${this.migrationReport.totalPlayers}`);
        console.log(`需要迁移的玩家数: ${this.migrationReport.playersWithExcessDeputies}`);
        console.log(`迁移的副将总数: ${this.migrationReport.totalDeputiesMoved}`);
        console.log(`错误数量: ${this.migrationReport.errors.length}`);

        if (this.migrationReport.errors.length > 0) {
            console.log('\n❌ 错误详情:');
            this.migrationReport.errors.forEach((error, index) => {
                console.log(`   ${index + 1}. ${error}`);
            });
        }

        if (this.migrationReport.details.length > 0) {
            console.log('\n📋 迁移详情:');
            this.migrationReport.details.forEach((detail, index) => {
                console.log(`   ${index + 1}. 玩家ID: ${detail.playerId}, 迁移: ${detail.moved}个, 保留: ${detail.kept}个`);
            });
        }

        return this.migrationReport;
    }

    /**
     * 执行完整迁移流程
     */
    async runMigration() {
        try {
            console.log('🚀 开始副将仓库数据迁移...');

            // 1. 检查需要迁移的玩家
            const excessPlayers = await this.checkDeputyCounts();
            this.migrationReport.totalPlayers = excessPlayers.length;
            this.migrationReport.playersWithExcessDeputies = excessPlayers.length;

            if (excessPlayers.length === 0) {
                console.log('✅ 没有需要迁移的数据，所有玩家副将数量都正常');
                return this.generateReport();
            }

            // 2. 逐个迁移玩家数据
            for (const player of excessPlayers) {
                try {
                    const result = await this.migratePlayerDeputies(player.player_id, player.deputy_count);
                    this.migrationReport.totalDeputiesMoved += result.moved;
                    this.migrationReport.details.push({
                        playerId: player.player_id,
                        moved: result.moved,
                        kept: result.kept,
                        movedDeputies: result.movedDeputies
                    });
                } catch (error) {
                    this.migrationReport.errors.push(`玩家 ${player.player_id}: ${error.message}`);
                }
            }

            // 3. 验证迁移结果
            const isValid = await this.verifyMigration();

            // 4. 生成报告
            const report = this.generateReport();

            if (isValid) {
                console.log('\n🎉 迁移完成！所有数据已成功迁移到仓库表');
            } else {
                console.log('\n⚠️ 迁移完成，但验证时发现问题，请检查数据');
            }

            return report;

        } catch (error) {
            console.error('❌ 迁移过程中发生错误:', error);
            this.migrationReport.errors.push(`系统错误: ${error.message}`);
            return this.generateReport();
        }
    }
}

// 主函数
async function main() {
    const migrator = new DeputyWarehouseMigrator();

    try {
        await migrator.connect();
        const report = await migrator.runMigration();

        // 保存报告到文件
        const fs = require('fs');
        const reportFile = `deputy-warehouse-migration-report-${new Date().toISOString().replace(/[:.]/g, '-')}.json`;
        fs.writeFileSync(reportFile, JSON.stringify(report, null, 2));
        console.log(`\n📄 详细报告已保存到: ${reportFile}`);

    } catch (error) {
        console.error('❌ 迁移失败:', error);
        process.exit(1);
    } finally {
        await migrator.disconnect();
    }
}

// 如果直接运行此脚本
if (require.main === module) {
    main().catch(console.error);
}

module.exports = DeputyWarehouseMigrator;
